package gob.osinerg.emergencia.dao;

import gob.osinerg.emergencia.bean.Dominio;
import gob.osinerg.emergencia.bean.Formulario;
import gob.osinerg.emergencia.bean.FormularioEmergDetalle;
import gob.osinerg.emergencia.bean.FormularioEmergencia;
import gob.osinerg.emergencia.bean.MailBean;
import gob.osinerg.emergencia.util.Constants;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateTemplate;

public class FormularioEmergenciaDAOimpl extends HibernateTemplate implements FormularioEmergenciaDAO{
	private Logger log = Logger.getLogger(FormularioEmergenciaDAOimpl.class);
	public List findEmergencia(FormularioEmergencia bean) throws Exception {
		
		Criteria criteria = getSession().createCriteria(FormularioEmergencia.class);
		criteria.createAlias("formulario", "f");
		if(!StringUtils.isEmpty(bean.getNmroEmrgncia())){
			criteria.add(Restrictions.like("nmroEmrgncia", "'%"+bean.getNmroEmrgncia()+"%'"));
		}
		if(!StringUtils.isEmpty(bean.getFormulario().getTpoInfrme())){
			criteria.add(Restrictions.eq("f.tpoInfrme", bean.getFormulario().getTpoInfrme()));
		}
		if(bean.getFchaEmrgncia()!= null){
			criteria.add(Restrictions.eq("fchaEmrgncia", bean.getFchaEmrgncia()));
		}
		if(!StringUtils.isEmpty(bean.getUsrioCrdor())){
			criteria.add(Restrictions.eq("usrioCrdor", bean.getUsrioCrdor()));
		}
		criteria.addOrder(Order.asc("nmroEmrgncia"));
		return criteria.list();
	}

	public String getNumeroMesaParte(Long uniopeId, Long unifisId) throws Exception {
		String nroMesaPartes="0";
		Connection con = null;
		CallableStatement cstmt = null;
        String sql = "{ call SFHDESA02.EMRGNCIAS_MSA_PRTES_STD(?,?,?,?,?)}";
        
		try{
			cstmt = getSession().connection().prepareCall(sql);
			cstmt.setString(1, "Informe de Emergencias");
			cstmt.setInt(2, Integer.parseInt(String.valueOf(uniopeId)));
			cstmt.setString(3, "B2");
			cstmt.setInt(4, Integer.parseInt(String.valueOf(unifisId)));
			cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
			cstmt.execute();
			nroMesaPartes = String.valueOf(cstmt.getInt(5));
            cstmt.close();
            cstmt = null;
            
		}catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (cstmt != null) {
                 try {
                	 cstmt.close();
                 }
                 catch (SQLException ignore) {
                 }
                 cstmt = null;
             }	
        }
		return nroMesaPartes;

	}

	public void saveMesaParteArchivos(Long id, String nroMesaParte)	throws Exception {
		
		String hsql = "UPDATE FormularioAnexo SET nroMesaParte = ? WHERE frmlrioEmergId = ? ";
		
		Query qr = getSession().createQuery(hsql);
		qr.setString(0, nroMesaParte);
		qr.setLong(1, Long.parseLong(String.valueOf(id)));
		int i = qr.executeUpdate();
		log.debug("Cantidad de Registros actualizados : "+i);
		
	}

	public boolean getExisteFormularioFinal(FormularioEmergencia bean)
			throws Exception {
		
		Criteria criteria = getSession().createCriteria(FormularioEmergencia.class);
		criteria.createAlias("formulario", "f");
		criteria.add(Restrictions.eq("nmroEmrgncia", bean.getNmroEmrgncia()));
		criteria.add(Restrictions.eq("tpoEmrgncia", bean.getTpoEmrgncia()));
		criteria.add(Restrictions.eq("f.tpoInfrme", Constants.TIPO_INFORME_FINAL));
		
		
		return criteria.list().size()>0?true:false;
	}
	
	public List getCategorias(String idFormulario) throws Exception {
		List lista = new ArrayList();
		Connection con = null;
        ResultSet rs = null;
        PreparedStatement pre = null;
        
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT   						       ");
		sql.append("		 F.ID        AS FORMU_ID     , ");
		//sql.append("         FE.ID   	 AS FORMU_EMG_ID , ");
		sql.append("         FC.ID       AS FORMU_CAT_ID , ");
		sql.append("         C.ID        AS CAT_ID       , ");
		sql.append("         C.DSCRPCION AS CATEGORIA ");
		sql.append("FROM     SFHDESA02.SFH_FRMLRIO F         , ");
		//sql.append("         SFH_FRMLRIO_EMRGNCIA FE, ");
		sql.append("         SFHDESA02.SFH_FRMLRIO_CTGRIA FC , ");
		sql.append("         SFHDESA02.SFH_CTGRIA C ");
		sql.append("WHERE    1            = 1 ");
		//sql.append("AND      FE.FRMLRIO_ID  = F.ID ");
		sql.append("AND      F.ID         = FC.FRMLRIO_ID ");
		sql.append("AND      FC.CTGRIA_ID = C.ID ");
		//sql.append("AND      FE.ID         = "+idFormularioEmrg+" ");
		sql.append("AND      F.ID         = "+idFormulario);
		sql.append("ORDER BY C.ORDEN");
		
		try{
			 pre = getSession().connection().prepareStatement(sql.toString());
	         rs = pre.executeQuery();
	        while (rs.next()) {
				lista.add(ResulsetToMap(rs));			
			}
	        pre.close();
            pre = null;
		}catch (Exception e) {
			log.error(e);
		}finally{
			 if (pre != null) {
	                try {
	                    pre.close();
	                }
	                catch (SQLException ignore) {
	                }
	                pre = null;
	            }
		}
		return lista;
	}
	
	public List getOpciones(String idFormulario, String idCategoria, String idSeccion)throws Exception {
		List lista = new ArrayList();
		Connection con = null;
        ResultSet rs = null;
        PreparedStatement pre = null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT   							  ");
		//sql.append("	     FE.ID   AS FORMU_EMG_ID    , ");
		sql.append("         F.ID    AS FORMU_ID		, ");
		sql.append("         FC.ID   AS FORMU_CAT_ID, ");
		sql.append("         CS.ID   AS CAT_SECC_ID , ");
		sql.append("         C.ID    AS CAT_ID      , ");
		sql.append(" 		 S.ID 	 AS SECC_ID		, ");
		sql.append("         O.ID    AS OPCION_ID   , ");
		sql.append("         O.NMBRE||'_'||F.ID||'_'||S.ID AS OPCIONNAME, ");
		sql.append("         O.DSCRPCION		    , ");
		sql.append("		 O.NMBRE AS TIPO		, ");
		sql.append("         O.ORDEN                , ");
		sql.append("         SO.WIDTH               , ");
		sql.append("         SO.HEIGTH              , ");
		sql.append("         SO.MAXLENGTH           , ");
		sql.append("         CS.RQUERDO AS REQUERIDO, ");
		sql.append("         C.DSCRPCION AS CATEGORIA, ");
		sql.append("         OT.TAG ");
		sql.append("FROM     SFHDESA02.SFH_FRMLRIO F         , ");
		//sql.append("         SFHDESA02.SFH_FRMLRIO_EMRGNCIA FE, ");
		sql.append("         SFHDESA02.SFH_FRMLRIO_CTGRIA FC , ");
		sql.append("         SFHDESA02.SFH_CTGRIA C          , ");
		sql.append("         SFHDESA02.SFH_CTGRIA_SCCION CS  , ");
		sql.append("         SFHDESA02.SFH_SCCION S          , ");
		sql.append("         SFHDESA02.SFH_SCCION_OPCION SO  , ");
		sql.append("         SFHDESA02.SFH_OPCION O          , ");
		sql.append("         SFHDESA02.SFH_TPO_OPCION OT ");
		sql.append("WHERE    1              = 1 ");
		//sql.append("AND      FE.FRMLRIO_ID  = F.ID ");
		sql.append("AND      F.ID           = FC.FRMLRIO_ID ");
		sql.append("AND      FC.CTGRIA_ID   = C.ID ");
		sql.append("AND      C.ID           = CS.CTGRIA_ID ");
		sql.append("AND      CS.SCCION_ID   = S.ID ");
		sql.append("AND      S.ID           = SO.SCCION_ID ");
		sql.append("AND      SO.OPCION_ID   = O.ID ");
		sql.append("AND      O.TP_OPCION_ID = OT.ID ");		
		//sql.append("AND      FE.ID        = "+idFormularioEmrg+" ");
		sql.append("AND    	 F.ID         = "+idFormulario+" ");
		sql.append("AND      C.ID         = "+idCategoria+" ");
		sql.append("AND      S.ID         = "+idSeccion+" ");
		sql.append("ORDER BY O.ORDEN");
		try{
			pre = getSession().connection().prepareStatement(sql.toString());
	        rs = pre.executeQuery();
	        while (rs.next()) {
				lista.add(ResulsetToMap(rs));			
			}
	        pre.close();
            pre = null;
		}catch (Exception e) {
			log.error(e);
		}finally{
			 if (pre != null) {
	                try {
	                    pre.close();
	                }
	                catch (SQLException ignore) {
	                }
	                pre = null;
	            }
		}
		return lista;
	}
	
	public List getSecciones(String idFormulario, String idCategoria) throws Exception{
		List lista = new ArrayList();
		Connection con = null;
        ResultSet rs = null;
        PreparedStatement pre = null;
        
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT 								");
		sql.append("	   F.ID        AS FORMU_ID    , ");
		//sql.append("       FE.ID   	   AS FORMU_EMG_ID, ");
		sql.append("       FC.ID       AS FORMU_CAT_ID, ");
		sql.append("       CS.ID       AS CAT_SECC_ID , ");
		sql.append("       C.ID        AS CAT_ID      , ");
		sql.append("       S.ID        AS SECC_ID     , ");
		sql.append("       S.DSCRPCION AS SECCION     , ");
		sql.append("       CS.RQUERDO  AS REQUERIDO ");
		sql.append("FROM   SFHDESA02.SFH_FRMLRIO F          , ");
		//sql.append("       SFH_FRMLRIO_EMRGNCIA FE, ");
		sql.append("       SFHDESA02.SFH_FRMLRIO_CTGRIA FC , ");
		sql.append("       SFHDESA02.SFH_CTGRIA C          , ");
		sql.append("       SFHDESA02.SFH_CTGRIA_SCCION CS  , ");
		sql.append("       SFHDESA02.SFH_SCCION S ");
		sql.append("WHERE  1            = 1 ");
		//sql.append("AND    FE.FRMLRIO_ID  = F.ID ");
		sql.append("AND    F.ID         = FC.FRMLRIO_ID ");
		sql.append("AND    FC.CTGRIA_ID = C.ID ");
		sql.append("AND    C.ID         = CS.CTGRIA_ID ");
		sql.append("AND    CS.SCCION_ID = S.ID ");
		//sql.append("AND    FE.ID        = "+idFormularioEmrg+" ");
		sql.append("AND    F.ID         = "+idFormulario+" ");
		sql.append("AND    C.ID         = "+idCategoria+" ");
		try{
			con = getSession().connection();
			pre = con.prepareStatement(sql.toString());
	        rs = pre.executeQuery();
	        while (rs.next()) {
				lista.add(ResulsetToMap(rs));			
			}
	        pre.close();
            pre = null;
            con.close();
            con = null;
		}catch (Exception e) {
			log.error(e);
		}finally{
			if(con != null){
				try{
					con.close();
					con = null;
				}catch (Exception e) {
					log.error(e);
					con = null;
				}
			}
			 if (pre != null) {
	                try {
	                    pre.close();
	                }
	                catch (SQLException ignore) {
	                }
	                pre = null;
	            }
		}
		return lista;
	}
	
	public Object ResulsetToMap(ResultSet rs) throws Exception {
		Map map = new HashMap();
		ResultSetMetaData meta = rs.getMetaData();
		int numberOfColumns = meta.getColumnCount();
		for (int i = 1; i <= numberOfColumns; ++i) {
			String name = meta.getColumnName(i);
			Object value = rs.getObject(i);
			map.put(name, value);
		}
		return map;
	}

	public String getNumeroEmergencia(Long unifisId) throws Exception {
		String nroEmergencia="0";
        ResultSet rs = null;
        PreparedStatement pstm = null;
		StringBuffer sql = new StringBuffer();
		sql.append("  SELECT NVL(MAX(TO_NUMBER(NMRO_EMRGNCIA)),0)+1 ");
		sql.append("  FROM   SFHDESA02.SFH_FRMLRIO_EMRGNCIA ");
		sql.append("  WHERE  1         = 1 ");
		sql.append("  AND    UNIFIS_ID = ? ");
		try{
			pstm = getSession().connection().prepareStatement(sql.toString());
        	pstm.setLong(1,Long.parseLong(String.valueOf(unifisId)));

	        rs = pstm.executeQuery();
	        while (rs.next()) {
				nroEmergencia = rs.getString(1);		
			}
	        pstm.close();
	        pstm = null;
		}catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (pstm != null) {
                 try {
                	 pstm.close();
                 }
                 catch (SQLException ignore) {
                 }
                 pstm = null;
             }	
        }
		return nroEmergencia;
	}

	public void grabarInformeRespuestas(FormularioEmergDetalle bean)throws Exception {
		save(bean);
		
	}

	public Long getUniFisIdParaUsuario(String codigo) throws Exception {
		Long uniFisId= new Long("0");
		Connection con = null;
        ResultSet rs = null;
        PreparedStatement pstm = null;
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT UNIFIS_ID ");
        sql.append("FROM   SFHDESA02.SFH_FRMLRIO ");
        sql.append("WHERE  1         = 1 ");
        sql.append("AND    ACTIVI_ID = ? ");
		try{
			pstm = getSession().connection().prepareStatement(sql.toString());
        	pstm.setLong(1,Long.parseLong(codigo));
	        rs = pstm.executeQuery();
	        while (rs.next()) {
	        	uniFisId = new Long(rs.getString(1));		
			}
	        pstm.close();
	        pstm = null;
		}catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (pstm != null) {
                 try {
                	 pstm.close();
                 }
                 catch (SQLException ignore) {
                 }
                 pstm = null;
             }	
        }
		return uniFisId;
	}
	
	public Map getCamposReporte(Long id)throws Exception {
		List lista = new ArrayList();
		Map mapa = new HashMap();
		ResultSet rs = null;
        PreparedStatement pstm = null;
        
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ID           , ");
		sql.append("       FRMLRIO_ID   , ");
		sql.append("       SCCION_ID    , ");
		sql.append("       CMPO         , ");
		sql.append("       TPO_FRMLRIO  , ");
		sql.append("       FCHA_MDFCCION, ");
		sql.append("       USRIO_MDFCDOR, ");
		sql.append("       FCHA_CRCION  , ");
		sql.append("       USRIO_CRDOR ");
		sql.append("FROM   SFHDESA02.SFH_CMPOS_RPRTE ");
		sql.append("WHERE   1 = 1		   ");
		sql.append("AND   	FRMLRIO_ID = ?   ");
		
		try{
	    	pstm = getSession().connection().prepareStatement(sql.toString());
	    	pstm.setLong(1, Long.parseLong(String.valueOf(id)));
	    	rs = pstm.executeQuery();
	    	while(rs.next()){
	    		mapa.put(rs.getString("SCCION_ID"), rs.getString("CMPO"));
	    		//lista.add(ResulsetToMap(rs));
	    	}
	    	pstm.close();
	    	rs = null;
	    }catch (Exception e) {
			log.error(e);
		}finally{
       	 if (pstm != null) {
             try {
            	 pstm.close();
             }
             catch (SQLException ignore) {
             }
             pstm = null;
       	 	}	
		}
		return mapa;
	}
	
	public List getCategoriaConsulta(Long idFormularioEmrg) throws Exception{
		List lista = new ArrayList();
        ResultSet rs = null;
        PreparedStatement pstm = null;
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT   FE.ID       AS FORMU_EMRG_ID, ");
        sql.append("         F.ID        AS FORMU_ID     , ");
        sql.append("         FC.ID       AS FORMU_CAT_ID , ");
        sql.append("         C.ID        AS CAT_ID       , ");
        sql.append("         C.DSCRPCION AS CATEGORIA ");
        sql.append("FROM     SFHDESA02.SFH_FRMLRIO F         , ");
        sql.append("         SFHDESA02.SFH_FRMLRIO_CTGRIA FC , ");
        sql.append("         SFHDESA02.SFH_CTGRIA C          , ");
        sql.append("         SFHDESA02.SFH_FRMLRIO_EMRGNCIA FE ");
        sql.append("WHERE    1             = 1 ");
        sql.append("AND      FE.FRMLRIO_ID = F.ID ");
        sql.append("AND      F.ID          = FC.FRMLRIO_ID ");
        sql.append("AND      FC.CTGRIA_ID  = C.ID ");
        sql.append("AND      FE.ID         = ? ");
        sql.append("ORDER BY C.ORDEN");
        try{
        	pstm = getSession().connection().prepareStatement(sql.toString());
        	pstm.setLong(1, Long.parseLong(String.valueOf(idFormularioEmrg)));
	        rs = pstm.executeQuery();
	        while (rs.next()) {
				lista.add(ResulsetToMap(rs));			
			}
	        pstm.close();
	        pstm = null;
        }catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (pstm != null) {
                 try {
                	 pstm.close();
                 }
                 catch (SQLException ignore) {
                 }
                 pstm = null;
             }	
        }
		return lista;
	}
	
	public List getSeccionConsulta(Long idFormularioEmrg, String idCategoria)throws Exception {
		List lista = new ArrayList();
        ResultSet rs = null;
        PreparedStatement pstm = null;
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT FE.ID AS FORMU_EMRG_ID, ");
        sql.append("       F.ID  AS FORMU_ID     , ");
        sql.append("       FC.ID AS FORMU_CAT_ID , ");
        sql.append("       CS.ID AS CAT_SECC_ID  , ");
        sql.append("       C.DSCRPCION           , ");
        sql.append("       C.ID        AS CAT_ID        , ");
        sql.append("       S.ID        AS SECC_ID       , ");
        sql.append("       S.DSCRPCION AS SECCION ");
        sql.append("FROM   SFHDESA02.SFH_FRMLRIO F         , ");
        sql.append("       SFHDESA02.SFH_FRMLRIO_CTGRIA FC , ");
        sql.append("       SFHDESA02.SFH_CTGRIA C          , ");
        sql.append("       SFHDESA02.SFH_CTGRIA_SCCION CS  , ");
        sql.append("       SFHDESA02.SFH_SCCION S          , ");
        sql.append("       SFHDESA02.SFH_FRMLRIO_EMRGNCIA FE ");
        sql.append("WHERE  1             = 1 ");
        sql.append("AND    FE.FRMLRIO_ID = F.ID ");
        sql.append("AND    F.ID          = FC.FRMLRIO_ID ");
        sql.append("AND    FC.CTGRIA_ID  = C.ID ");
        sql.append("AND    C.ID          = CS.CTGRIA_ID ");
        sql.append("AND    CS.SCCION_ID  = S.ID ");
        sql.append("AND    FE.ID         = ? ");
        sql.append("AND    C.ID          = ? ");
        try{
        	pstm = getSession().connection().prepareStatement(sql.toString());
        	pstm.setLong(1, Long.parseLong(String.valueOf(idFormularioEmrg)));
        	pstm.setLong(2, Long.parseLong(idCategoria));
        	
	        rs = pstm.executeQuery();
	        while (rs.next()) {
				lista.add(ResulsetToMap(rs));			
			}
	        pstm.close();
	        pstm = null;
        }catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (pstm != null) {
                 try {
                	 pstm.close();
                 }
                 catch (SQLException ignore) {
                 }
                 pstm = null;
             }	
        }
		return lista;
	}
	
	
	public List getFormularioRespuestas(Long idFormularioEmrg, String idSeccion) throws Exception{
		List lista = new ArrayList();
		Connection con = null;
        ResultSet rs = null;
        PreparedStatement pstm = null;
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT O.DSCRPCION, ");
        sql.append("       FD.VLOR    , ");
        sql.append("       S.DSCRPCION  ");
        sql.append("FROM   SFHDESA02.SFH_FRMLRIO_EMER_DTLLE FD, ");
        sql.append("       SFHDESA02.SFH_OPCION O             , ");
        sql.append("       SFHDESA02.SFH_FRMLRIO_EMRGNCIA FE  , ");
        sql.append("       SFHDESA02.SFH_SCCION S ");
        sql.append("WHERE  1               = 1 ");
        sql.append("AND    FD.FORM_EMER_ID = FE.ID ");
        sql.append("AND    FD.OPCION_ID    = O.ID  ");
        sql.append("AND    FD.SCCION_ID    = S.ID  ");
        sql.append("AND    FD.FORM_EMER_ID = ? ");
        sql.append("AND    FD.SCCION_ID    = ? ");
        try{
        	pstm = getSession().connection().prepareStatement(sql.toString());
        	pstm.setLong(1, Long.parseLong(String.valueOf(idFormularioEmrg)));
        	pstm.setLong(2, Long.parseLong(idSeccion));
        	
	        rs = pstm.executeQuery();
	        while (rs.next()) {
				lista.add(ResulsetToMap(rs));			
			}
	        pstm.close();
	        pstm = null;
        }catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (pstm != null) {
                 try {
                	 pstm.close();
                 }
                 catch (SQLException ignore) {
                 }
                 pstm = null;
             }	
        }
		return lista;
	}

	public void procesarMail(MailBean mailBean) throws Exception {
		ResourceBundle rb = ResourceBundle.getBundle("gob.osinerg.resources.sendMail");
		String ipHost =rb.getString("mail.ip");
		String portHost = rb.getString("mail.port");
		
		String sql="{call TESTMAIL(?,?,?,?,?,?,?,?)}";
		CallableStatement cs=null;
		
		try{
		   cs = getSession().connection().prepareCall(sql);
           cs.setString(1, ipHost);
           cs.setInt(2, Integer.parseInt(portHost));
           cs.setString(3,mailBean.getFromName());
           cs.setString(4,mailBean.getFromMail());
           cs.setString(5,mailBean.getToName());
           cs.setString(6,mailBean.getToMail());
           cs.setString(7, mailBean.getSubject());
           cs.setString(8, mailBean.getMensaje());

           cs.execute();
           cs.close();
           cs = null;
		}catch (Exception e) {
			log.error(e);
		}finally{
			if(cs != null){
				cs.close();
			}
		}
		
	}

	public List getUsuariosParaEnvioMail(Formulario formulario)
			throws Exception {
		
		List lista = new ArrayList();
		Connection con = null;
        ResultSet rs = null;
        PreparedStatement pstm = null;
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ID           , ");
        sql.append("       FCHA_MDFCCION, ");
        sql.append("       USRIO_MDFCDOR, ");
        sql.append("       FCHA_CRCION  , ");
        sql.append("       USRIO_CRDOR  , ");
        sql.append("       MAIL         , ");
        sql.append("       CDGO_USRIO   , ");
        sql.append("       ACTIVI_ID    , ");
        sql.append("       UNIFIS_ID ");
        sql.append("FROM   SFHDESA02.SFH_CRREO_EMRGNCIA ");
        sql.append("WHERE  1 = 1				");
        sql.append("AND   ACTIVI_ID = "+formulario.getActiviId()+" ");
        sql.append("AND   UNIFIS_ID = "+formulario.getUnifisId()+" ");
		
		try{
			pstm = getSession().connection().prepareStatement(sql.toString());

			rs =pstm.executeQuery();
			while (rs.next()){

				lista.add(ResulsetToMap(rs));
			}
			pstm.close();
			rs = null;
		}catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (pstm != null) {
                 try {
                	 pstm.close();
                 }
                 catch (SQLException ignore) {
                 }
                 pstm = null;
             }	
        }
        return lista;
	}

	public String getZonaFiscaliazable(Long uniopeId, String uniFisId)
			throws Exception {
		String cadena = "";
		String sql = "SELECT SFHDESA02.NSFH_20300("+uniopeId+", "+uniFisId+") FROM DUAL ";
		try{
			Query qr = getSession().createSQLQuery(sql);
			cadena = qr.uniqueResult().toString();
		}catch (Exception e) {
			log.error(e);
		}
		return cadena;
	}

	public List getMailSupervisor(String zonaFisId) throws Exception {
		
		List lista = new ArrayList();
		Connection con = null;
        ResultSet rs = null;
        PreparedStatement pstm = null;
        
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT MAIL_SPRVSR_RGNAL ");
		sql.append("FROM   SFHDESA02.SFH_ZNAXSPRVSR_RGNAL ");
		sql.append("WHERE  zounfi_id = ? ");
		sql.append("AND    estdo     = 0	");
		log.debug(sql.toString());
		try{
			pstm = getSession().connection().prepareStatement(sql.toString());
			pstm.setLong(1, Long.parseLong(zonaFisId));
			rs =pstm.executeQuery();
			while (rs.next()){
				lista.add(ResulsetToMap(rs));
			}
			pstm.close();
			rs = null;
		}catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (pstm != null) {
                 try {
                	 pstm.close();
                 }
                 catch (SQLException ignore) {
                 }
                 pstm = null;
             }	
        }
        return lista;
	}

	public List getBusquedaTipoEmergencia(String dominio, String abreviation)
			throws Exception {
		List lista = new ArrayList();
		ResultSet rs = null;
	    PreparedStatement pstm = null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT RV_LOW_VALUE, ");
		sql.append("       RV_MEANING ");
		sql.append("FROM   SFHDESA02.CG_REF_CODES ");
		sql.append("WHERE  RV_DOMAIN    = '"+dominio+"' ");
		sql.append("AND    RV_ABBREVIATION = '"+abreviation+"'");
		try{
        	pstm = getSession().connection().prepareStatement(sql.toString());
        	rs = pstm.executeQuery();
        	while(rs.next()){
        		Dominio bean = new Dominio();
        		bean.setId(rs.getString("RV_LOW_VALUE"));
        		bean.setDescripcion(rs.getString("RV_MEANING"));
        		lista.add(bean);
        		
        	}
        	pstm.close();
	        pstm = null;
		}catch (Exception e) {
			log.error(e);
        }finally{
        	 if (pstm != null) {
                 try {
                	 pstm.close();
                 }
                 catch (SQLException ignore) {
                 }
                 pstm = null;
             }	
        }
		
		return lista;
	}

	public List getListaGeneral(Long id) throws Exception {
		List lista = new ArrayList();
        ResultSet rs = null;
        PreparedStatement pstm = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT F.ID        AS FORMU_ID         , ");
        sql.append("        FC.ID       AS FORMU_CAT_ID     , ");
        sql.append("        CS.ID       AS CAT_SECC_ID      , ");
        sql.append("        C.ID        AS CAT_ID           , ");
        sql.append("        S.ID        AS SECC_ID          , ");
        sql.append("        O.ID||'_'||S.ID AS OPCION_ID        , ");
        sql.append("        O.ID AS OPCION_ID2        , ");
        sql.append("        O.DSCRPCION AS OPCION_DSCRPCION , ");
        sql.append("        O.NMBRE||'_'||F.ID||'_'||S.ID     AS OPCION_TIPO      , ");
        sql.append("        O.ORDEN     AS OPCION_ORDEN     , ");
        sql.append("        SO.WIDTH                        , ");
        sql.append("        SO.HEIGTH                       , ");
        sql.append("        SO.MAXLENGTH                    , ");
        sql.append("        CS.RQUERDO  AS REQUERIDO         , ");
        sql.append("        C.DSCRPCION AS CAT_DSCRPCION     , ");
        sql.append("        C.ORDEN     AS CAT_ORDEN         , ");
        sql.append("        S.DSCRPCION AS SECC_DESCRIPCION  , ");
        sql.append("        OT.TAG      AS TIPO_OPC_TAG      , ");
        sql.append("        OT.ID       AS TIPO_OPC_ID ");
        sql.append(" FROM   SFHDESA02.SFH_FRMLRIO F         , ");
        sql.append("        SFHDESA02.SFH_FRMLRIO_CTGRIA FC , ");
        sql.append("        SFHDESA02.SFH_CTGRIA C          , ");
        sql.append("        SFHDESA02.SFH_CTGRIA_SCCION CS  , ");
        sql.append("        SFHDESA02.SFH_SCCION S          , ");
        sql.append("        SFHDESA02.SFH_SCCION_OPCION SO  , ");
        sql.append("        SFHDESA02.SFH_OPCION O          , ");
        sql.append("        SFHDESA02.SFH_TPO_OPCION OT ");
        sql.append(" WHERE  1              = 1 ");
        sql.append(" AND    F.ID           = FC.FRMLRIO_ID ");
        sql.append(" AND    FC.CTGRIA_ID   = C.ID ");
        sql.append(" AND    C.ID           = CS.CTGRIA_ID(+) ");
        sql.append(" AND    CS.SCCION_ID   = S.ID(+) ");
        sql.append(" AND    S.ID           = SO.SCCION_ID(+) ");
        sql.append(" AND    SO.OPCION_ID   = O.ID(+) ");
        sql.append(" AND    O.TP_OPCION_ID = OT.ID(+) ");
        sql.append(" AND    F.ID           = ? ");
        sql.append(" ORDER BY S.ID,O.ID    ");
		try{
        	pstm = getSession().connection().prepareStatement(sql.toString());
        	pstm.setLong(1, Long.parseLong(String.valueOf(id)));
	        rs = pstm.executeQuery();
	        while (rs.next()) {
				lista.add(ResulsetToMap(rs));			
			}
	        pstm.close();
	        pstm = null;
        }catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (pstm != null) {
                 try {
                	 pstm.close();
                 }
                 catch (SQLException ignore) {
                 }
                 pstm = null;
             }	
        }
		return lista;
	}

	public List getListaRespuestasGeneral(Long id) throws Exception {
		List lista = new ArrayList();
        ResultSet rs = null;
        PreparedStatement pstm = null;
        StringBuffer sql = new StringBuffer();
        sql.append("  SELECT   FE.ID       AS FORMU_EMRG_ID   , ");
        sql.append("           F.ID        AS FORMU_ID        , ");
        sql.append("           S.ID        AS SECC_ID         , ");
        sql.append("           O.ID        AS OPCION_ID       , ");
        sql.append("           C.ID        AS CAT_ID          , ");
        sql.append("           C.DSCRPCION AS CAT_DSCRPCION   , ");
        sql.append("           S.DSCRPCION AS SECC_DESCRIPCION, ");
        sql.append("           FED.VLOR    AS RESPUESTA ");
        sql.append("  FROM     SFHDESA02.SFH_FRMLRIO_EMRGNCIA FE, ");
        sql.append("           SFHDESA02.SFH_FRMLRIO F          , ");
        sql.append("           SFHDESA02.SFH_FRMLRIO_CTGRIA FC  , ");
        sql.append("           SFHDESA02.SFH_CTGRIA C           , ");
        sql.append("           SFHDESA02.SFH_CTGRIA_SCCION CS   , ");
        sql.append("           SFHDESA02.SFH_SCCION S           , ");
        sql.append("           SFHDESA02.SFH_SCCION_OPCION SO   , ");
        sql.append("           SFHDESA02.SFH_OPCION O           , ");
        sql.append("           SFHDESA02.SFH_FRMLRIO_EMER_DTLLE FED ");
        sql.append("  WHERE    1                = 1 ");
        sql.append("  AND      FE.FRMLRIO_ID    = F.ID ");
        sql.append("  AND      F.ID             = FC.FRMLRIO_ID ");
        sql.append("  AND      FC.CTGRIA_ID     = C.ID (+) ");
        sql.append("  AND      C.ID             = CS.CTGRIA_ID(+) ");
        sql.append("  AND      CS.SCCION_ID     = S.ID(+) ");
        sql.append("  AND      S.ID             = SO.SCCION_ID(+) ");
        sql.append("  AND      SO.OPCION_ID     = O.ID (+) ");
        sql.append("  AND      FED.FORM_EMER_ID = FE.ID ");
        sql.append("  AND      FED.OPCION_ID    = O.ID ");
        sql.append("  AND      FED.SCCION_ID    = S.ID ");
        sql.append("  AND      FE.ID            = ? ");
        sql.append("  ORDER BY S.ID");
		try{
        	pstm = getSession().connection().prepareStatement(sql.toString());
        	pstm.setLong(1, Long.parseLong(String.valueOf(id)));
	        rs = pstm.executeQuery();
	        while (rs.next()) {
				lista.add(ResulsetToMap(rs));			
			}
	        pstm.close();
	        pstm = null;
        }catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (pstm != null) {
                 try {
                	 pstm.close();
                 }
                 catch (SQLException ignore) {
                 }
                 pstm = null;
             }	
        }
		return lista;
	}

}
